1 Imports System.Data.SqlClient
2 Public Class frmNoDues_Docs
3 Dim Status As String
4 Sub fillSession()
5 Try
6 con = New SqlConnection(cs)
7 con.Open()
8 adp = New SqlDataAdapter()
9 adp.SelectCommand = New SqlCommand("SELECT distinct (Session) FROM Student", con)
10 ds = New DataSet("ds")
11 adp.Fill(ds)
12 dtable = ds.Tables(0)
13 cmbSession.Items.Clear()
14 For Each drow As DataRow In dtable.Rows
15 cmbSession.Items.Add(drow(0).ToString())
16 Next
17 Catch ex As Exception
18 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
19 End Try
20 End Sub
21 Sub fillDepartment()
22 Try
23 con = New SqlConnection(cs)
24 con.Open()
25 adp = New SqlDataAdapter()
26 adp.SelectCommand = New SqlCommand("SELECT DepartmentName from Staff,NoDues_Staff,Department,Staff_Department where Staff.St_ID=NoDues_Staff.StaffID and Staff.St_ID=Staff_Department.StaffID and Department.ID=Staff_Department.DepartmentID", con)
27 ds = New DataSet("ds")
28 adp.Fill(ds)
29 dtable = ds.Tables(0)
30 cmbDepartment.Items.Clear()
31 For Each drow As DataRow In dtable.Rows
32 cmbDepartment.Items.Add(drow(0).ToString())
33 Next
34 Catch ex As Exception
35 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
36 End Try
37 End Sub
38 Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
39 Me.Close()
40 End Sub
41 Sub Reset()
42 cmbClass.SelectedIndex = -1
43 cmbSection.SelectedIndex = -1
44 cmbSession.SelectedIndex = -1
45 listView1.Items.Clear()
46 cmbClass.Enabled = False
47 cmbSection.Enabled = False
48 btnUpdate.Enabled = False
49 End Sub
50 Private Sub btnNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNew.Click
51 Reset()
52 End Sub
53
54 Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
55 Try
56 If listView1.Items.Count = 0 Then
57 MessageBox.Show("Sorry nothing to update.." & vbCrLf & "Please retrieve data in listview", "", MessageBoxButtons.OK, MessageBoxIcon.Error)
58 Exit Sub
59 End If
60 For i As Integer = listView1.Items.Count - 1 To 0 Step -1
61 con = New SqlConnection(cs)
62 If listView1.Items(i).Checked = True Then
63 Status = "Yes"
64 Else
65 Status = "No"
66 End If
67 Dim cd As String = "update NoDues_Student set Status= '" & Status & "' where AdmissionNo=@d1"
68 cmd = New SqlCommand(cd)
69 cmd.Parameters.AddWithValue("@d1", listView1.Items(i).SubItems(0).Text)
70 cmd.Connection = con
71 con.Open()
72 cmd.ExecuteNonQuery()
73 con.Close()
74 Next
75 MessageBox.Show("Successfully updated", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
76 btnUpdate.Enabled = False
77 Catch ex As Exception
78 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
79 End Try
80 End Sub
81
82
83 Private Sub cmbSession_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cmbSession.SelectedIndexChanged
84 Try
85 cmbClass.Enabled = True
86 con = New SqlConnection(cs)
87 con.Open()
88 Dim ct As String = "SELECT distinct RTRIM(ClassName) FROM Student,Section,Class where Student.SectionID=Section.ID and Section.Class=Class.ClassName and Session=@d1"
89 cmd = New SqlCommand(ct)
90 cmd.Connection = con
91 cmd.Parameters.AddWithValue("@d1", cmbSession.Text)
92 rdr = cmd.ExecuteReader()
93 cmbClass.Items.Clear()
94 While rdr.Read
95 cmbClass.Items.Add(rdr(0))
96 End While
97 con.Close()
98 Catch ex As Exception
99 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
100 End Try
101 End Sub
102
103 Private Sub cmbClass_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cmbClass.SelectedIndexChanged
104 Try
105 cmbSection.Enabled = True
106 con = New SqlConnection(cs)
107 con.Open()
108 Dim ct As String = "SELECT distinct RTRIM(SectionName) FROM Student,Section,Class where Student.SectionID=Section.ID and Section.Class=Class.ClassName and Session=@d1 and ClassName=@d2"
109 cmd = New SqlCommand(ct)
110 cmd.Connection = con
111 cmd.Parameters.AddWithValue("@d1", cmbSession.Text)
112 cmd.Parameters.AddWithValue("@d2", cmbClass.Text)
113 rdr = cmd.ExecuteReader()
114 cmbSection.Items.Clear()
115 While rdr.Read
116 cmbSection.Items.Add(rdr(0))
117 End While
118 con.Close()
119 Catch ex As Exception
120 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
121 End Try
122 End Sub
123
124 Private Sub Button1_Click(sender As System.Object, e As System.EventArgs)
125 Try
126 If Len(Trim(cmbSession.Text)) = 0 Then
127 MessageBox.Show("Please select session", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
128 cmbSession.Focus()
129 Exit Sub
130 End If
131 If Len(Trim(cmbClass.Text)) = 0 Then
132 MessageBox.Show("Please select class", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
133 cmbClass.Focus()
134 Exit Sub
135 End If
136 If Len(Trim(cmbSection.Text)) = 0 Then
137 MessageBox.Show("Please select section", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
138 cmbSection.Focus()
139 Exit Sub
140 End If
141 con = New SqlConnection(cs)
142 con.Open()
143 cmd = New SqlCommand("select AdmissionNo,StudentName From from Student,ClassInfo,Section,SchoolInfo where Student.SectionID=Section.ID and ClassInfo.ClassName=Section.Class and SchoolInfo.S_ID=Student.SchoolID and Session=@d1 and ClassName=@d2 and SectionName=@d3 order by StudentName", con)
144 cmd.Parameters.AddWithValue("@d1", cmbSession.Text)
145 cmd.Parameters.AddWithValue("@d2", cmbClass.Text)
146 cmd.Parameters.AddWithValue("@d3", cmbSection.Text)
147 rdr = cmd.ExecuteReader()
148 While rdr.Read()
149 Dim item = New ListViewItem()
150 item.Text = rdr(0).ToString().Trim()
151 item.SubItems.Add(rdr(1).ToString().Trim())
152 listView1.Items.Add(item)
153 End While
154 Catch ex As Exception
155 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
156 End Try
157 End Sub
158
159 Private Sub btnGetData_Click(sender As System.Object, e As System.EventArgs) Handles btnSearch.Click
160 Try
161 btnUpdate.Enabled = True
162 If Len(Trim(cmbSession.Text)) = 0 Then
163 MessageBox.Show("Please select session", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
164 cmbSession.Focus()
165 Exit Sub
166 End If
167 If Len(Trim(cmbClass.Text)) = 0 Then
168 MessageBox.Show("Please select class", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
169 cmbClass.Focus()
170 Exit Sub
171 End If
172 If Len(Trim(cmbSection.Text)) = 0 Then
173 MessageBox.Show("Please select section", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
174 cmbSection.Focus()
175 Exit Sub
176 End If
177 con = New SqlConnection(cs)
178 con.Open()
179 cmd = New SqlCommand("select Student.AdmissionNo,StudentName,NoDues_Student.Status from Student,Class,Section,SchoolInfo,NoDues_Student where Student.SectionID=Section.ID and Class.ClassName=Section.Class and SchoolInfo.S_ID=Student.SchoolID and Student.AdmissionNo=NoDues_Student.AdmissionNo and Session=@d1 and ClassName=@d2 and SectionName=@d3 and Student.Status='Active' order by StudentName", con)
180 cmd.Parameters.AddWithValue("@d1", cmbSession.Text)
181 cmd.Parameters.AddWithValue("@d2", cmbClass.Text)
182 cmd.Parameters.AddWithValue("@d3", cmbSection.Text)
183 rdr = cmd.ExecuteReader()
184 While rdr.Read()
185 Dim item = New ListViewItem()
186 item.Text = rdr(0).ToString().Trim()
187 item.SubItems.Add(rdr(1).ToString().Trim())
188 item.SubItems.Add(rdr(2).ToString().Trim())
189 listView1.Items.Add(item)
190 For i As Integer = listView1.Items.Count - 1 To 0 Step -1
191 If listView1.Items(i).SubItems(2).Text = "Yes" Then
192 listView1.Items(i).Checked = True
193 Else
194 listView1.Items(i).Checked = False
195 End If
196 Next
197 End While
198 Catch ex As Exception
199 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
200 End Try
201 End Sub
202
203 Private Sub frmDiscount_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
204 fillSession()
205 fillDepartment()
206 End Sub
207
208 Private Sub BtnClose1_Click(sender As System.Object, e As System.EventArgs) Handles BtnClose1.Click
209 Me.Close()
210 End Sub
211 Sub Reset1()
212 cmbDepartment.SelectedIndex = -1
213 ListView2.Items.Clear()
214 btnUpdate1.Enabled = False
215 End Sub
216 Private Sub btnNew1_Click(sender As System.Object, e As System.EventArgs) Handles btnNew1.Click
217 Reset1()
218 End Sub
219
220 Private Sub Button5_Click(sender As System.Object, e As System.EventArgs) Handles Button5.Click
221 Try
222 btnUpdate1.Enabled = True
223 If Len(Trim(cmbDepartment.Text)) = 0 Then
224 MessageBox.Show("Please select department", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
225 cmbDepartment.Focus()
226 Exit Sub
227 End If
228 con = New SqlConnection(cs)
229 con.Open()
230 cmd = New SqlCommand("select St_ID,Staff.StaffID,StaffName,NoDues_Staff.Status from Staff,NoDues_Staff,Department,Staff_Department where Staff.St_ID=NoDues_Staff.StaffID and Staff.St_ID=Staff_Department.StaffID and Department.ID=Staff_Department.DepartmentID and DepartmentName=@d1 and Staff.Status='Active' order by StaffName", con)
231 cmd.Parameters.AddWithValue("@d1", cmbDepartment.Text)
232 rdr = cmd.ExecuteReader()
233 While rdr.Read()
234 Dim item = New ListViewItem()
235 item.Text = rdr(0).ToString().Trim()
236 item.SubItems.Add(rdr(1).ToString().Trim())
237 item.SubItems.Add(rdr(2).ToString().Trim())
238 item.SubItems.Add(rdr(3).ToString().Trim())
239 ListView2.Items.Add(item)
240 For i As Integer = ListView2.Items.Count - 1 To 0 Step -1
241 If ListView2.Items(i).SubItems(3).Text = "Yes" Then
242 ListView2.Items(i).Checked = True
243 Else
244 ListView2.Items(i).Checked = False
245 End If
246 Next
247 End While
248 Catch ex As Exception
249 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
250 End Try
251 End Sub
252
253 Private Sub btnUpdate1_Click(sender As System.Object, e As System.EventArgs) Handles btnUpdate1.Click
254 Try
255 If ListView2.Items.Count = 0 Then
256 MessageBox.Show("Sorry nothing to update.." & vbCrLf & "Please retrieve data in listview", "", MessageBoxButtons.OK, MessageBoxIcon.Error)
257 Exit Sub
258 End If
259 For i As Integer = ListView2.Items.Count - 1 To 0 Step -1
260 con = New SqlConnection(cs)
261 If ListView2.Items(i).Checked = True Then
262 Status = "Yes"
263 Else
264 Status = "No"
265 End If
266 Dim cd As String = "update NoDues_Staff set Status= '" & Status & "' where StaffID=@d1"
267 cmd = New SqlCommand(cd)
268 cmd.Parameters.AddWithValue("@d1", ListView2.Items(i).SubItems(0).Text)
269 cmd.Connection = con
270 con.Open()
271 cmd.ExecuteNonQuery()
272 con.Close()
273 Next
274 btnUpdate1.Enabled = False
275 MessageBox.Show("Successfully updated", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
276 Catch ex As Exception
277 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
278 End Try
279 End Sub
280 End Class
